{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Wall time: 1.15 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "import warnings\n",
    "import pandas as pd\n",
    "from datetime import datetime\n",
    "from dateutil.parser import parse\n",
    "from sklearn.preprocessing import OneHotEncoder\n",
    "\n",
    "warnings.filterwarnings('ignore')\n",
    "pd.set_option('display.width', 180)  # 150，设置打印宽度\n",
    "pd.set_option('display.max_rows', None) # 打印最大行数\n",
    "pd.set_option('display.max_columns', 40) # 打印最大列数"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Wall time: 1.49 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "path = r\"G:\\LKM\\competition\\二手车交易价格预测\\data\"\n",
    "train = pd.read_csv(path + r\"\\used_car_train_20200313.csv\", sep=' ') #读取训练集\n",
    "test = pd.read_csv(path + r\"\\used_car_testB_20200421.csv\", sep=' ') #读取测试集"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Wall time: 427 ms\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "### 新增年、月、日三列字段\n",
    "def map_date(data):\n",
    "    return data.map(lambda x: str(x)[:4]), data.map(lambda x: str(x)[4:6]), data.map(lambda x: str(x)[6:])\n",
    "\n",
    "### 汽车上线时间 -- creatDate_year\n",
    "train['creatDate_year'], train['creatDate_month'], train['creatDate_day']  = map_date(train['creatDate'])\n",
    "test['creatDate_year'], test['creatDate_month'], test['creatDate_day']  = map_date(test['creatDate'])\n",
    "### 汽车注册日期 -- regDate\n",
    "train['regDate_year'], train['regDate_month'], train['regDate_day']  = map_date(train['regDate'])\n",
    "test['regDate_year'], test['regDate_month'], test['regDate_day']  = map_date(test['regDate'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Wall time: 8.39 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "### 异常值处理\n",
    "def error_month(data):\n",
    "    data[data == '00'] = '01'\n",
    "    return data.map(lambda x: '12' if int(x) > 12 else x)\n",
    "\n",
    "train['creatDate_month'] = error_month(train['creatDate_month'])\n",
    "test['creatDate_month'] = error_month(test['creatDate_month'])\n",
    "train['regDate_month'] = error_month(train['regDate_month'])\n",
    "test['regDate_month'] = error_month(test['regDate_month'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Wall time: 267 ms\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "### 新增特征列creatDate、regDate\n",
    "train['creatDate'] = train['creatDate_year'] + '/' + train['creatDate_month'] + '/' + train['creatDate_day']\n",
    "test['creatDate'] = test['creatDate_year'] + '/' + test['creatDate_month'] + '/' + test['creatDate_day']\n",
    "\n",
    "train['regDate'] = train['regDate_year'] + '/' + train['regDate_month'] + '/' + train['regDate_day']\n",
    "test['regDate'] = test['regDate_year'] + '/' + test['regDate_month'] + '/' + test['regDate_day']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Wall time: 137 ms\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "### 删除辅助列\n",
    "cols = ['creatDate_year', 'creatDate_month', 'creatDate_day', 'regDate_year', 'regDate_month', 'regDate_day']\n",
    "for col in cols:\n",
    "    del train[col]\n",
    "    del test[col]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Wall time: 2.99 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "### 计算销售日期与注册时间间隔天数\n",
    "def interval(series1, series2):\n",
    "    series1 = pd.to_datetime(series1)\n",
    "    series2 = pd.to_datetime(series2)\n",
    "    return (series1 - series2).map(lambda x: x.days)\n",
    "\n",
    "train['car_days'] = interval(train['creatDate'], train['regDate'])\n",
    "test['car_days'] = interval(test['creatDate'], test['regDate'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Wall time: 4.96 ms\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "### 删除regDate与creatDate\n",
    "del train['regDate']\n",
    "del train['creatDate']\n",
    "del test['regDate']\n",
    "del test['creatDate']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Wall time: 85.8 ms\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "### 缺失值填充与异常值填充\n",
    "train['model'] = train['model'].fillna(train['model'].mode()[0])\n",
    "train['bodyType'] = train['bodyType'].fillna(train['bodyType'].mode()[0])\n",
    "train['fuelType'] = train['fuelType'].fillna(train['fuelType'].mode()[0])\n",
    "train['gearbox'] = train['gearbox'].fillna(train['gearbox'].mode()[0])\n",
    "a = train['notRepairedDamage'].mode()[0]\n",
    "train['notRepairedDamage'] = train['notRepairedDamage'].map(lambda x: a if x == '-' else x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Wall time: 0 ns\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>SaleID</th>\n",
       "      <th>name</th>\n",
       "      <th>model</th>\n",
       "      <th>brand</th>\n",
       "      <th>bodyType</th>\n",
       "      <th>fuelType</th>\n",
       "      <th>gearbox</th>\n",
       "      <th>power</th>\n",
       "      <th>kilometer</th>\n",
       "      <th>notRepairedDamage</th>\n",
       "      <th>regionCode</th>\n",
       "      <th>seller</th>\n",
       "      <th>offerType</th>\n",
       "      <th>price</th>\n",
       "      <th>v_0</th>\n",
       "      <th>v_1</th>\n",
       "      <th>v_2</th>\n",
       "      <th>v_3</th>\n",
       "      <th>v_4</th>\n",
       "      <th>v_5</th>\n",
       "      <th>v_6</th>\n",
       "      <th>v_7</th>\n",
       "      <th>v_8</th>\n",
       "      <th>v_9</th>\n",
       "      <th>v_10</th>\n",
       "      <th>v_11</th>\n",
       "      <th>v_12</th>\n",
       "      <th>v_13</th>\n",
       "      <th>v_14</th>\n",
       "      <th>car_days</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>736</td>\n",
       "      <td>30.0</td>\n",
       "      <td>6</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>60</td>\n",
       "      <td>12.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1046</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1850</td>\n",
       "      <td>43.357796</td>\n",
       "      <td>3.966344</td>\n",
       "      <td>0.050257</td>\n",
       "      <td>2.159744</td>\n",
       "      <td>1.143786</td>\n",
       "      <td>0.235676</td>\n",
       "      <td>0.101988</td>\n",
       "      <td>0.129549</td>\n",
       "      <td>0.022816</td>\n",
       "      <td>0.097462</td>\n",
       "      <td>-2.881803</td>\n",
       "      <td>2.804097</td>\n",
       "      <td>-2.420821</td>\n",
       "      <td>0.795292</td>\n",
       "      <td>0.914762</td>\n",
       "      <td>4385</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>2262</td>\n",
       "      <td>40.0</td>\n",
       "      <td>1</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4366</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3600</td>\n",
       "      <td>45.305273</td>\n",
       "      <td>5.236112</td>\n",
       "      <td>0.137925</td>\n",
       "      <td>1.380657</td>\n",
       "      <td>-1.422165</td>\n",
       "      <td>0.264777</td>\n",
       "      <td>0.121004</td>\n",
       "      <td>0.135731</td>\n",
       "      <td>0.026597</td>\n",
       "      <td>0.020582</td>\n",
       "      <td>-4.900482</td>\n",
       "      <td>2.096338</td>\n",
       "      <td>-1.030483</td>\n",
       "      <td>-1.722674</td>\n",
       "      <td>0.245522</td>\n",
       "      <td>4757</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>14874</td>\n",
       "      <td>115.0</td>\n",
       "      <td>15</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>163</td>\n",
       "      <td>12.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2806</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>6222</td>\n",
       "      <td>45.978359</td>\n",
       "      <td>4.823792</td>\n",
       "      <td>1.319524</td>\n",
       "      <td>-0.998467</td>\n",
       "      <td>-0.996911</td>\n",
       "      <td>0.251410</td>\n",
       "      <td>0.114912</td>\n",
       "      <td>0.165147</td>\n",
       "      <td>0.062173</td>\n",
       "      <td>0.027075</td>\n",
       "      <td>-4.846749</td>\n",
       "      <td>1.803559</td>\n",
       "      <td>1.565330</td>\n",
       "      <td>-0.832687</td>\n",
       "      <td>-0.229963</td>\n",
       "      <td>4382</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>71865</td>\n",
       "      <td>109.0</td>\n",
       "      <td>10</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>193</td>\n",
       "      <td>15.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>434</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2400</td>\n",
       "      <td>45.687478</td>\n",
       "      <td>4.492574</td>\n",
       "      <td>-0.050616</td>\n",
       "      <td>0.883600</td>\n",
       "      <td>-2.228079</td>\n",
       "      <td>0.274293</td>\n",
       "      <td>0.110300</td>\n",
       "      <td>0.121964</td>\n",
       "      <td>0.033395</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>-4.509599</td>\n",
       "      <td>1.285940</td>\n",
       "      <td>-0.501868</td>\n",
       "      <td>-2.438353</td>\n",
       "      <td>-0.478699</td>\n",
       "      <td>7125</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>111080</td>\n",
       "      <td>110.0</td>\n",
       "      <td>5</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>68</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6977</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>5200</td>\n",
       "      <td>44.383511</td>\n",
       "      <td>2.031433</td>\n",
       "      <td>0.572169</td>\n",
       "      <td>-1.571239</td>\n",
       "      <td>2.246088</td>\n",
       "      <td>0.228036</td>\n",
       "      <td>0.073205</td>\n",
       "      <td>0.091880</td>\n",
       "      <td>0.078819</td>\n",
       "      <td>0.121534</td>\n",
       "      <td>-1.896240</td>\n",
       "      <td>0.910783</td>\n",
       "      <td>0.931110</td>\n",
       "      <td>2.834518</td>\n",
       "      <td>1.923482</td>\n",
       "      <td>1531</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   SaleID    name  model  brand  bodyType  fuelType  gearbox  power  kilometer notRepairedDamage  regionCode  seller  offerType  price        v_0       v_1       v_2       v_3  \\\n",
       "0       0     736   30.0      6       1.0       0.0      0.0     60       12.5               0.0        1046       0          0   1850  43.357796  3.966344  0.050257  2.159744   \n",
       "1       1    2262   40.0      1       2.0       0.0      0.0      0       15.0               0.0        4366       0          0   3600  45.305273  5.236112  0.137925  1.380657   \n",
       "2       2   14874  115.0     15       1.0       0.0      0.0    163       12.5               0.0        2806       0          0   6222  45.978359  4.823792  1.319524 -0.998467   \n",
       "3       3   71865  109.0     10       0.0       0.0      1.0    193       15.0               0.0         434       0          0   2400  45.687478  4.492574 -0.050616  0.883600   \n",
       "4       4  111080  110.0      5       1.0       0.0      0.0     68        5.0               0.0        6977       0          0   5200  44.383511  2.031433  0.572169 -1.571239   \n",
       "\n",
       "        v_4       v_5       v_6       v_7       v_8       v_9      v_10      v_11      v_12      v_13      v_14  car_days  \n",
       "0  1.143786  0.235676  0.101988  0.129549  0.022816  0.097462 -2.881803  2.804097 -2.420821  0.795292  0.914762      4385  \n",
       "1 -1.422165  0.264777  0.121004  0.135731  0.026597  0.020582 -4.900482  2.096338 -1.030483 -1.722674  0.245522      4757  \n",
       "2 -0.996911  0.251410  0.114912  0.165147  0.062173  0.027075 -4.846749  1.803559  1.565330 -0.832687 -0.229963      4382  \n",
       "3 -2.228079  0.274293  0.110300  0.121964  0.033395  0.000000 -4.509599  1.285940 -0.501868 -2.438353 -0.478699      7125  \n",
       "4  2.246088  0.228036  0.073205  0.091880  0.078819  0.121534 -1.896240  0.910783  0.931110  2.834518  1.923482      1531  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%time\n",
    "train.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Wall time: 3.99 ms\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "array([0], dtype=int64)"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%time\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python [conda env:lkm]",
   "language": "python",
   "name": "lkm"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
